﻿using System;
using System.Collections.Generic;
using System.Text;
using ERP.DAL.Base;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Microsoft.Practices.EnterpriseLibrary.Common.Configuration;
using System.Data;
using System.Reflection;
using ERP.BusinessEntities.Util;
using System.Data.SqlClient;
using System.Data.Common;
using System.ComponentModel;
using ERP.BusinessEntities.Base;
using ERP.DAL.Util;
using ERP.BusinessEntities.Factory;
using Microsoft.Practices.EnterpriseLibrary.Caching;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;

namespace ERP.DAL
{
    public class EntDal:ADal,IDal 
    {
        
        public EntDal(Database _db, String _strTableName, SqlConnection _SqlConn, SqlDatabase _SqlDb)
            : base(_strTableName)
        {
            db = _db;
            SqlConn = _SqlConn;
            SqlDb = _SqlDb;
        }

        #region Read

        public DataSet GetDataset(String strQuery)
        {
            DataSet ds;
            ds = db.ExecuteDataSet(CommandType.Text, strQuery);

            return ds;
        }

        public Object GetObjectFromDataRow(DataRow datarow)
        {
            Object obj = lstInstanceHandler[StrTableName]();
            foreach (KeyValuePair<String, FieldInfo>  pair in lstFlds)
            {
                try
                {
                    lstSetHandler[StrTableName + "_set" + pair.Value.Name](obj, datarow[pair.Key]);
                }
                catch (Exception ex)
                {
                }
            }
            return obj;
        }

        public List<Object> GetListObject(DataSet ds)
        {
            List<Object> lst = new List<Object>();

            if (ds != null && ds.Tables[0] != null && ds.Tables[0].Rows.Count > 0)
            {
                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    Object obj = GetObjectFromDataRow(ds.Tables[0].Rows[i]);
                    lst.Add(obj);
                }
            }

            return lst;
        }

        public DataSet GetAllObjects()
        {
            DataSet ds = new DataSet();
            DbCommand selectAllCommand = PrepairSPCommand_In(db, null, StrTableName + "_SelectAll", null);
            db.LoadDataSet(selectAllCommand, ds, StrTableName);
            return ds;
        }

        public Object GetObjectById(int iObjectID)
        {
            DbCommand selectCommand = PrepairSPCommand_In(db, iObjectID, StrTableName + "_Select", strPrimaryKey);

            DataSet ds = db.ExecuteDataSet(selectCommand);

            if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
            {
                return GetObjectFromDataRow(ds.Tables[0].Rows[0]);
            }

            return null;
        }

        public DataSet GetAllObjectByColumnValue(String strColumn, object value)
        {
            String strQuery = String.Empty;
            if(value is int || value is double)
                strQuery = String.Format("SELECT * FROM {0} WHERE {1} = {2}", StrTableName, strColumn, value);
            else
                strQuery = String.Format("SELECT * FROM {0} WHERE {1} = '{2}'", StrTableName, strColumn, value.ToString());

            String strQueryWithAAStatus = strQuery + " AND AAStatus = 'Alive'";

            DataSet ds = new DataSet();
            try
            {
                ds = db.ExecuteDataSet(CommandType.Text, strQueryWithAAStatus);
            }
            catch (Exception ex)
            {
                ds = db.ExecuteDataSet(CommandType.Text, strQuery);
            }

            return ds;
        }

        public DataSet GetDataSetByCondition(string strConditions)
        {
            return GetCustomDataSetByCondition("*", strConditions);
        }

        public DataSet GetCustomDataSetByCondition(string strSelectClause, string strConditions)
        {
            String strQuery = String.Format(@"SELECT {0} FROM {1} WHERE {2}", strSelectClause, StrTableName, strConditions);
            return GetDataset(strQuery);
        }
        #endregion

        #region Update

        public int Update(BaseBusinessInfo obj)
        {
            String strSP = StrTableName + "_Update";
            DbCommand UpdateCommand = PrepairSPCommand_In(db, obj, strSP, lstFlds);
            db.ExecuteNonQuery(UpdateCommand);
            return (int)UpdateCommand.Parameters["@" + strPrimaryKey].Value;
        }
        
        public void UpdateList(List<BaseBusinessInfo> lstObj)
        {
            foreach (BaseBusinessInfo obj in lstObj)
            {
                Update(obj);
            }
        }
        #endregion

        #region Insert
        public int CreateObject(BaseBusinessInfo obj)
        {
            int MaxID = GetMaxID(StrTableName);
            lstSetHandler[StrTableName +"_set" + strFieldPrimaryKey](obj, MaxID + 1);
            String strSP = StrTableName + "_Insert";
            DbCommand InsertCommand = PrepairSPCommand_In(db, obj, strSP, lstFlds);
            db.ExecuteNonQuery(InsertCommand);
            return MaxID + 1;
        } 
        #endregion

        #region Delete
        public void PermanentDeleteObject(BaseBusinessInfo Obj)
        {
            DbCommand DeleteCommand = db.GetStoredProcCommand(StrTableName + "_Delete");
            db.AddInParameter(DeleteCommand, "@" + strPrimaryKey, DbType.Int32, DynamicObject.GetProperty(Obj, lstFlds[strPrimaryKey]));
            db.ExecuteNonQuery(DeleteCommand);
        }
        public void Delete(BaseBusinessInfo Obj)
        {
            Delete((int)DynamicObject.GetProperty(Obj, lstFlds[strPrimaryKey]));
        }

        public void Delete(int iObjID)
        {
            DbCommand UpdateCommand = db.GetStoredProcCommand(StrTableName + "_DeleteByID");
            db.AddInParameter(UpdateCommand, "@" + strPrimaryKey, DbType.Int32, iObjID);
            db.ExecuteNonQuery(UpdateCommand);
        }

        public void DeleteAllByParentID(String strForeignColumn, int iParentID)
        {
            String strQuery = String.Format("UPDATE {0} SET {1} = 'Delete' WHERE {2} = {3}", StrTableName, "AAStatus", strForeignColumn, iParentID);
            try
            {
                db.ExecuteNonQuery(CommandType.Text, strQuery);
            }
            catch (Exception ex)
            {
                strQuery = String.Format("DELETE {0} WHERE {1} = {2}", StrTableName, strForeignColumn, iParentID);
                db.ExecuteNonQuery(CommandType.Text, strQuery);
            }
            
        }
        #endregion


        public void GetListObject(DataSet ds, List<object> lst)
        {
            lst.Clear();
            if (ds != null && ds.Tables[0] != null && ds.Tables[0].Rows.Count > 0)
            {
                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    Object obj = GetObjectFromDataRow(ds.Tables[0].Rows[i]);
                    lst.Add(obj);
                }
            }
        }
        
    }
}
